<?php if (!defined('BASEPATH')) {
	exit('No direct script access allowed');
}

class Customer_locations_model extends CI_Model {
	public function __construct() {
		parent::__construct();
	}

  public function get_locations($zone){
    $sql="SELECT *,aea.zone_1
       , (SELECT(CASE WHEN ar.account_id in (SELECT account_id from ubs_meter_readings where CONCAT(bill_year,'-',bill_month) = '2015-11') THEN 'YES' ELSE 'NO' END)) as flag
       , umr.consumed
      FROM ar_account ar
        LEFT JOIN (SELECT account_id , consumed FROM ubs_meter_readings WHERE CONCAT(bill_year,'-',bill_month) = '2015-11') umr on umr.account_id = ar.account_id
        INNER JOIN ar_entity_address aea on ar.acct_addr = aea.address_id
      where ar.loc_latitude != 0 and ar.loc_longitude != 0 and ar.status = 'ACT' and aea.zone_1 = '".$zone."'
    ";
    $query = $this->db->query($sql);
    return $query->result();
  }

  public function getzoning() {
    $query = $this->db->get('ar_zone1_values_v');
    return $query->result();
  }

  public function getRanges(){
    $sql = "SELECT umr.* , alv.description  , alv.meaning
                FROM ubs_marker_range umr
                    INNER JOIN app_lookup_values alv on alv.lookup_code = umr.marker_code 
                ORDER BY umr.range_from ASC";
        $query = $this->db->query($sql);
        return $query->result();
  }


  // SELECT *,aea.zone_1
  //      , (SELECT(CASE WHEN ar.account_id in (SELECT account_id from ubs_meter_readings where CONCAT(bill_year,'-',bill_month) = DATE_FORMAT(NOW(),'%Y-%m')) THEN 'YES' ELSE 'NO' END)) as flag
  //      , umr.consumed
  //     FROM ar_account ar
  //       LEFT JOIN (SELECT account_id , consumed FROM ubs_meter_readings WHERE CONCAT(bill_year,'-',bill_month) = DATE_FORMAT(NOW(),'%Y-%m')) umr on umr.account_id = ar.account_id
  //       INNER JOIN ar_entity_address aea on ar.acct_addr = aea.address_id
  //     where ar.loc_latitude != 0 
  //     and ar.loc_longitude != 0 
  //     and ar.status = 'ACT' 
  //     and aea.zone_1 = 'CENTRO'


}
